<?php
namespace MyApp\Application\Model;

use DinhThi\MVC\Model\MySQLModel;
class KhachHangModel extends MySQLModel{
    public function __construct(){
        parent::connect();
    }

    public function TaoKhachHang($ten, $diachi, $sodienthoai){
        $sth = $this->DB->prepare("insert into khachhang (ten, diachi, sodienthoai) values (:ten, :diachi, :sodienthoai)");
        $sth->execute(array(
            ':ten'          => $ten,
            ':diachi'       => $diachi,
            ':sodienthoai'  => $sodienthoai,
        ));


        $sth = $this->DB->prepare('select LAST_INSERT_ID() as ID');
        $sth->execute();
        $id = $sth->fetch();
        $id = $id['ID'];
        return array('id' => $id, 'hoten' => $ten, 'sodienthoai' => $sodienthoai, 'diachi' => $diachi);
    }

    public function SuaKhachHang($id, $ten, $diachi, $sodienthoai){
        $sth = $this->DB->prepare("update khachhang set ten = :ten, diachi = :diachi, sodienthoai = :sodienthoai where id = :id");
        $sth->execute(array(
            ':id'           => $id,
            ':ten'          => $ten,
            ':diachi'       => $diachi,
            ':sodienthoai'  => $sodienthoai,
        ));

        return array('id' => $id, 'hoten' => $ten, 'sodienthoai' => $sodienthoai, 'diachi' => $diachi);
    }

    public function DanhSachKhachHang(){
        $sth = $this->DB->prepare("select * from khachhang order by id desc");
        $sth->execute();
        return $sth->fetchAll();
    }

    public function TimKiemKhachHang($value){
        $sth = $this->DB->prepare("select * from khachhang where ten like '%:value%' or diachi like '%:value' or sodienthoai like '%:value%' or congno >= :value");
        $sth->execute(
            array(
                ':value' => $value
            )
        );

        return $sth->fetchAll();
    }

    public function ThongTinGiaoDich($id){
        $sth = $this->DB->prepare("SELECT SUM(trigia) as TongGiaTri, count(*) as SoGiaoDich
                                    FROM hoadon
                                    WHERE nguoimua = :id
                                    GROUP BY nguoimua");
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetch();
    }

    public function ChiTietGiaoDich($id){
        $sth = $this->DB->prepare("select * from khachhang_congno where khachhang = :id order by id");
        $sth->execute(
            array(
                ':id' => $id
            )
        );
        return $sth->fetchAll();
    }

    public function QuyetToanCongNo($id, $sotien, $ghichu){
        $sth = $this->DB->prepare("insert into khachhang_congno (khachhang, sotien, ghichu) values (:id, - :sotien, :ghichu)");
        $sth->execute(
            array(
                ':id'       => $id,
                ':sotien'   => $sotien,
                ':ghichu'   => $ghichu
            )
        );
    }

    /*public function CapNhatCongNo($id, $no){
        $sth = $this->DB->prepare('UPDATE khachhang SET congno = (congno + :congno) WHERE id = :id');
        $sth->execute(
            array(
                ':id'       => $id,
                ':congno'   => $no
            )
        );
    }*/

} 